﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace MusicDatabase
{
    /*
     * Auteur:  Joris Molnár en Niels van Leusden
     * Datum:   31-1-2013
     * Omschrijving: Deze klasse vult het hoofdscherm en maakt de zoekfunctie mogelijk.
     * 
     */
    public class FillMain
    {
        /// <summary>
        /// Get the content to fill the DGV with
        /// </summary>
        /// <param name="dgvMain"></param>
        public void FillMainDgv(DataGridView dgvMain)
        {
            SQLServices sqlService = new SQLServices();

            string command = "SELECT album.alb_id, alb_name Album, art_name Artist, alb_date Year, alb_price Price, CONVERT(nvarchar, SUM(song.song_length)/60) + ':' + CASE WHEN SUM(song.song_length)%60<10 THEN '0' + CONVERT(nvarchar, SUM(song.song_length)%60) ELSE CONVERT(nvarchar, SUM(song.song_length)%60) END \"Total Length\" " +
                "FROM songrelease, artist, song, album " +
                "WHERE songrelease.song_id = song.song_id AND album.alb_id = songrelease.alb_id AND album.alb_artist = artist.art_id " +
                "GROUP BY album.alb_id, alb_name, art_name, alb_date, alb_price " +
                "ORDER BY alb_name, alb_id";
            List<string[]> content = sqlService.GetTable(command);

            //AlbumArtist(sqlService.GetTable(command));
            Fill(dgvMain, content);
        }

        /// <summary>
        /// Fill the DGV with the content
        /// </summary>
        /// <param name="dgvMain"></param>
        /// <param name="content"></param>
        private void Fill(DataGridView dgvMain, List<string[]> content)
        {
            for (int i = 0; i < content[0].Length; i++) // Fill the DGV
            {
                dgvMain.Columns.Add(content[0][i], content[0][i]);
            }
            for (int i = 1; i < content.Count; i++)
            {
                dgvMain.Rows.Add(content[i]);
            }
            dgvMain.Columns[0].Visible = false; // Make art_id invisible
            dgvMain.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            dgvMain.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;
            dgvMain.Columns[5].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight;

            dgvMain.Columns[1].Width = 296;
            dgvMain.Columns[2].Width = 217;
            dgvMain.Columns[3].Width = 50;
            dgvMain.Columns[4].Width = 65;
            dgvMain.Columns[5].Width = 90;
        }

        /// <summary>
        /// Search in ALBUM and show the result
        /// </summary>
        /// <param name="dgvMain"></param>
        /// <param name="album"></param>
        /// <param name="artist"></param>
        /// <param name="song"></param>
        public void SearchDgv(DataGridView dgvMain, string album, string artist, string song)
        {
            dgvMain.Columns.Clear();

            string command = "SELECT album.alb_id, alb_name Album, art_name Artist, alb_date Year, alb_price Price, CONVERT(nvarchar, SUM(song.song_length)/60) + ':' + CASE WHEN SUM(song.song_length)%60<10 THEN '0' + CONVERT(nvarchar, SUM(song.song_length)%60) ELSE CONVERT(nvarchar, SUM(song.song_length)%60) END \"Total Length\" " +
            "FROM songrelease, artist, song, album " +
            "WHERE songrelease.song_id = song.song_id AND album.alb_id = songrelease.alb_id AND album.alb_artist = artist.art_id AND alb_name LIKE '%" + album.Replace("'", "''") + "%' AND art_name LIKE '%" + artist.Replace("'", "''") + "%' AND song_name LIKE '%" + song.Replace("'", "''") + "%'" +
            "GROUP BY album.alb_id, alb_name, art_name, alb_date, alb_price " +
            "ORDER BY alb_name, alb_id";

            SQLServices sqlService = new SQLServices();
            List<string[]> content = sqlService.GetTable(command);

            Fill(dgvMain, content);
        }

        /// <summary>
        /// No longer need piece of code to show Various Artist if an album had songs with different artists
        /// </summary>
        /// <param name="content"></param>
        private void AlbumArtist(List<string[]> content) // Not needed anymore
        {
            for (int i = 1; i < content.Count; i++)  // If there are multiple artist who made one album, artist will become "Various Artist"
            {
                if (content[i - 1][0] == content[i][0])
                {
                    content[i][2] = "Various Artists";
                    content.Remove(content[i - 1]);  // Removes an item from the list, so the counter needs to go one back
                    i--;
                }
            }
        }

    }
}
